﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data ;
using Maticsoft.DBUtility;

namespace CustomsSystem.DAL
{
    public class BillViewService
    {
        /// <summary>
        /// 对账单，费用查询
        /// </summary>
        public BillViewService()
        {
        }

        public DataSet GetStatementOfAccount(string strWhere)
        {


            StringBuilder strsql = new StringBuilder();
            strsql.Append(@"select distinct(a.sheetID),b.CustomerName '客户名',a.transferNo1 '编号',a.clearanceDate '进出口日期',a.shipName '船名',a.No '数量',a.goodsName '货名',a.ladingBillNo '提单号',a.headLadingBillNo '头程提单号',a.customsNo '报关单号',a.inspectionNo '报检号'  ");
            strsql.Append(@" from T_sheetInfo a left join Cu_customer b on(a.cID=b.ID) right join T_bill c on(a.sheetID=c.sheetID)");

            if (strWhere != "")
            {
                strsql.Append(" where " + strWhere);
            }


            return DbHelperSQL.Query(strsql.ToString());
        }

        /// <summary>
        /// 结算单
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet Get(string strWhere)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(" select distinct(a.sheetID),d.CustomerName,a.customsNo, SS ,");
            strsql.Append("SFbao,SFdai from T_sheetInfo a ");
            strsql.Append("left join (select sheetid,  SUM(sfeeAmount)over(partition by sheetID) as SS from ");
            strsql.Append("T_bill where billTypeID=1) b on(a.sheetID=b.sheetID) ");
            strsql.Append(" left join (select sheetID,  SUM(sfeeAmount)over(partition by sheetID) as SFbao  ");
            strsql.Append("from T_bill where checkOut=1 and billTypeID=2) c on(a.sheetID=c.sheetID )  ");
            strsql.Append(" left join (select sheetID,  SUM(sfeeAmount)over(partition by sheetID) as SFdai ");
            strsql.Append(" from T_bill where checkOut=2 and billTypeID=2) e on(a.sheetID=e.sheetID ) ");
            strsql.Append("left join Cu_Customer d on (d.ID=a.cID) ");
            if (strWhere != "")
            {
                strsql.Append(" where " + strWhere);
            }
            //dt2包干转账表
            strsql.Append(" select distinct(a.sheetID),c.feeName, convert(varchar(10),a.inputDate,120), a.sfeeAmount, a.remark, a.invNO ");
            strsql.Append(" from T_bill a ");
            strsql.Append(" left join T_bill b on(a.sheetID=b.sheetID and b.billTypeID=2) ");
            strsql.Append(" inner join Info_feeType c on(a.feeTypeID=c.feeTypeNo ) ");

            if (strWhere != "")
            {
                strsql.Append(" where a.billTypeID =2 and a.checkOut=1 and a.payType=2 and " + strWhere);
            }
            //dt3代收代付转账表
            strsql.Append(" select distinct(a.sheetID),c.feeName,convert(varchar(10),a.inputDate,120), a.sfeeAmount,  a.remark , a.invNo ");
            strsql.Append(" from T_bill a ");
            strsql.Append(" left join T_bill b on(a.sheetID=b.sheetID and b.billTypeID=2) ");
            strsql.Append(" inner join Info_feeType c on(a.feeTypeID=c.feeTypeNo ) ");

            if (strWhere != "")
            {
                strsql.Append(" where a.billTypeID =2 and a.checkOut=2 and a.payType=2 and " + strWhere);
            }
            //dt4包干现金表
            strsql.Append(" select distinct(a.sheetID),c.feeName, convert(varchar(10),a.inputDate,120), a.sfeeAmount,a.remark, a.invNO ");
            strsql.Append(" from T_bill a ");
            strsql.Append(" left join T_bill b on(a.sheetID=b.sheetID and b.billTypeID=2) ");
            strsql.Append(" inner join Info_feeType c on(a.feeTypeID=c.feeTypeNo ) ");

            if (strWhere != "")
            {
                strsql.Append(" where a.billTypeID =2 and a.checkOut=1 and a.payType=1 and " + strWhere);
            }
            //dt5代收代付现金表
            strsql.Append(" select distinct(a.sheetID),c.feeName,convert(varchar(10),a.inputDate,120), a.sfeeAmount, a.remark , a.invNo ");
            strsql.Append(" from T_bill a ");
            strsql.Append(" left join T_bill b on(a.sheetID=b.sheetID and b.billTypeID=2) ");
            strsql.Append(" inner join Info_feeType c on(a.feeTypeID=c.feeTypeNo ) ");

            if (strWhere != "")
            {
                strsql.Append(" where a.billTypeID =2 and a.checkOut=2 and a.payType=1 and " + strWhere);
            }

            return DbHelperSQL.Query(strsql.ToString());
        }
    }
}
